﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using AccessProvider;

namespace Interface.Diem
{
    public partial class BangDiemMon : Form
    {
        DataConnection dc = new DataConnection();
        BusinessLogic bl = new BusinessLogic();
        string loaiUser = "";
        string monhoc;
        public BangDiemMon()
        {
            InitializeComponent();
        }

        private void BangDiemMon_Load(object sender, EventArgs e)
        {
            string userName = Main.loginName;
            string sqlLoaiUser = "select LoaiUser from Users where Username = '" + userName + "'";
            string sqltenMH = "select TenMH from Users u, MonHoc mh where u.Username = '" + userName + "' and u.MaMH=mh.MaMH";
            loaiUser = dc.getStringValue(sqlLoaiUser);
            monhoc = dc.getStringValue(sqltenMH);
            if (loaiUser != "GVIEN")
            {
                btnUpdate.Visible = false;
            }

            loadLop();
            loadMon();
            cbHK.Items.Add(1);
            cbHK.Items.Add(2);
            cbHK.SelectedIndex = 0;           
        }
        private void loadMon()
        {
            string sql = "select * from MonHoc";
            DataTable dt = dc.getData(sql);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    string tenMH = row["TenMH"].ToString();
                    cbMon.Items.Add(tenMH);
                }
            }
        }
        private void loadLop()
        {            
            string sqlLop = "select * from Lop";
            DataTable dt = dc.getData(sqlLop);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    string tenLop = row["TenLop"].ToString();
                    cbLop.Items.Add(tenLop);
                }
            }
        }        

        protected DataTable dtInitiate()
        {
            DataTable dt = new DataTable();
            DataColumn col = dt.Columns.Add("STT", typeof(string));
            col.AutoIncrement = true;
            col.AutoIncrementStep = 1;
            col.AutoIncrementSeed = 1;
            dt.Columns.Add("HoTen", typeof(string));
            dt.Columns.Add("Diem15", typeof(string));
            dt.Columns.Add("Diem1t", typeof(string));
            dt.Columns.Add("DiemTBHK", typeof(string));            
            return dt;
        }

        protected void loadDiem()
        {
            DataTable dt = new DataTable();
            if (cbLop.SelectedItem!= null && cbMon.SelectedItem!=null && cbHK.SelectedItem!=null )
            {
                string tenlop = cbLop.SelectedItem.ToString();
                string tenMH = cbMon.SelectedItem.ToString();
                int hk = Convert.ToInt32(cbHK.Text);
                int stt=0;

                string sql = "select * from Diem d, Lop l, MonHoc m where d.MaLop=l.MaLop and l.TenLop='{0}' and d.MaMH=m.MaMH and m.TenMH=N'{1}' and HocKy={2}";
                sql = string.Format(sql, tenlop, tenMH, hk);
                DataTable dtDiem = dc.getData(sql);
                dt = dtInitiate();
                if (dtDiem.Rows.Count > 0)
                {
                    for (int i = 0; i < dtDiem.Rows.Count; i++)
                    {
                        DataRow dr = dt.NewRow();
                        stt = i + 1;
                        dr["STT"] = stt;
                        string maHS = dtDiem.Rows[i]["MaHS"].ToString();
                        string sqltenHS = "select hs.HoTen from HocSinh hs, Diem d where d.MaHS = hs.MaHS and hs.MaHS='" + maHS + "'";
                        string tenHS = dc.getStringValue(sqltenHS);
                        dr["HoTen"] = tenHS;

                        decimal dDiem15=0;
                        decimal dDiem1t=0;
                        string strDiem15 = dtDiem.Rows[i]["Diem15p"] != null ? dtDiem.Rows[i]["Diem15p"].ToString() : string.Empty;
                        string strDiem1t = dtDiem.Rows[i]["Diem1Tiet"] != null ? dtDiem.Rows[i]["Diem1Tiet"].ToString() : string.Empty;

                        if (strDiem15 != string.Empty)
                        {
                            dDiem15 = Convert.ToDecimal(strDiem15);                            
                            dr["Diem15"] = dDiem15.ToString();
                        }
                        if (strDiem1t != string.Empty)
                        {
                            dDiem1t = Convert.ToDecimal(strDiem1t);
                            dr["Diem1t"] = dDiem1t.ToString();
                        }                        
                                                                                               
                        if (strDiem15 != string.Empty && strDiem1t != string.Empty)
                        {
                            decimal dhk = (dDiem15 + (dDiem1t * 2)) / 3;
                            dhk = Math.Round(dhk, 1);
                            dr["DiemTBHK"] = dhk.ToString();
                        }
                        
                        dt.Rows.Add(dr);
                    }
                }
                string sql2 = @"select hs.MaHS from HocSinh hs, Lop l where l.TenLop='{0}' and hs.MaLop=l.MaLop except " +
                            "select d.MaHS from Diem d, HocSinh hs, Lop l, MonHoc mh where d.MaLop=hs.MaLop and hs.MaLop=l.MaLop and l.TenLop='{0}' "+
                            "and mh.MaMH=d.MaMH and mh.TenMH=N'{1}' and HocKy={2}";
                sql2 = string.Format(sql2, tenlop, tenMH, hk);
                DataTable dtDiem2 = dc.getData(sql2);
                if (dtDiem2.Rows.Count > 0)
                {
                    for (int i = 0; i < dtDiem2.Rows.Count; i++)
                    {
                        stt = stt + 1;
                        DataRow dr2 = dt.NewRow();
                        string maHS = dtDiem2.Rows[i]["MaHS"].ToString();
                        string sqltenHS = "select HoTen from HocSinh where MaHS='" + maHS + "'";
                        string tenHS = dc.getStringValue(sqltenHS);
                        dr2["STT"] = stt;
                        dr2["HoTen"] = tenHS;
                        dt.Rows.Add(dr2);
                    }
                }
                dgvDiem.DataSource = dt;
            }                        
        }        

        protected void cbLop_SelectedIndexChanged(object sender, EventArgs e)
        {
            loadDiem();
        }

        private void cbMon_SelectedIndexChanged(object sender, EventArgs e)
        {
            loadDiem();
        }

        private void cbHK_SelectedIndexChanged(object sender, EventArgs e)
        {
            loadDiem();
        }

        private void dgvDiem_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvDiem.SelectedRows.Count == 0) return;
            lblHoTen.Text = dgvDiem.SelectedRows[0].Cells[1].Value.ToString();
            txtDiem15p.Text = dgvDiem.SelectedRows[0].Cells[2].Value.ToString();
            txtDiem1T.Text = dgvDiem.SelectedRows[0].Cells[3].Value.ToString();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {                                 
            if (txtDiem15p.Text != string.Empty || txtDiem1T.Text != string.Empty)
            {
                
                if (monhoc == cbMon.SelectedItem.ToString())
                {
                    string tenlop = cbLop.SelectedItem.ToString();
                    string tenMH = cbMon.SelectedItem.ToString();
                    int hk = Convert.ToInt32(cbHK.Text);

                    string tenHS = lblHoTen.Text;
                    string strDiem15p = txtDiem15p != null ? txtDiem15p.Text : string.Empty;
                    string strDiem1T = txtDiem1T != null ? txtDiem1T.Text : string.Empty;

                    decimal dDiem15p = 0;
                    decimal dDiem1T = 0;
                    if (strDiem15p != string.Empty)
                    {
                        if (bl.Isnumeric(strDiem15p) == false)
                        {
                            MessageBox.Show("Điểm 15' phải là dữ liệu số");
                            return;
                        }
                        dDiem15p = Convert.ToDecimal(strDiem15p);
                        if (dDiem15p > 10)
                        {
                            MessageBox.Show("Điểm 15 phút không được lớn hơn 10");
                            return;
                        }
                    }
                    if (strDiem1T != string.Empty)
                    {
                        if (bl.Isnumeric(strDiem1T) == false)
                        {
                            MessageBox.Show("Điểm 1 tiết phải là dữ liệu số");
                            return;
                        }
                        dDiem1T = Convert.ToDecimal(strDiem1T);
                        if (dDiem1T > 10)
                        {
                            MessageBox.Show("Điểm 1 tiết phút không được lớn hơn 10");
                            return;
                        }
                    }

                    string sqlMaHS = "select MaHS from HocSinh where HoTen=N'" + tenHS + "'";
                    string sqlMaMH = "select MaMH from MonHoc where TenMH=N'" + tenMH + "'";
                    string sqlMaLop = "select MaLop from Lop where TenLop='" + tenlop + "'";

                    string maHS = dc.getStringValue(sqlMaHS);
                    string maMH = dc.getStringValue(sqlMaMH);
                    int maLop = Convert.ToInt32(dc.getNumberValue(sqlMaLop));

                    string sqlCheck = "select * from Diem where MaHS='{0}' and MaMH='{1}' and MaLop={2} and HocKy={3}";
                    sqlCheck = string.Format(sqlCheck, maHS, maMH, maLop, hk);
                    DataTable dtcheck = dc.getData(sqlCheck);
                    if (dtcheck.Rows.Count > 0)
                    {
                        string sqlUpdate = "";
                        if (txtDiem15p.Text == string.Empty && txtDiem1T.Text != string.Empty)
                        {
                            sqlUpdate = "update Diem set Diem1Tiet={0} where MaHS='{1}' and MaMH='{2}' and MaLop={3} and HocKy={4}";
                            sqlUpdate = string.Format(sqlUpdate, dDiem1T, maHS, maMH, maLop, hk);
                        }
                        else if (txtDiem15p.Text != string.Empty && txtDiem1T.Text == string.Empty)
                        {
                            sqlUpdate = "update Diem set Diem15p={0} where MaHS='{1}' and MaMH='{2}' and MaLop={3} and HocKy={4}";
                            sqlUpdate = string.Format(sqlUpdate, dDiem15p, maHS, maMH, maLop, hk);
                        }
                        else
                        {
                            sqlUpdate = "update Diem set Diem15p={0}, Diem1Tiet={1} where MaHS='{2}' and MaMH='{3}' and MaLop={4} and HocKy={5}";
                            sqlUpdate = string.Format(sqlUpdate, dDiem15p, dDiem1T, maHS, maMH, maLop, hk);
                        }
                        dc.ExecNonQuery(sqlUpdate);
                    }
                    else
                    {
                        string sqlInsert = "";
                        if (txtDiem15p.Text == string.Empty && txtDiem1T.Text != string.Empty)
                        {
                            sqlInsert = "insert into Diem values('{0}','{1}',{2},{3},null,{4})";
                            sqlInsert = string.Format(sqlInsert, maHS, maMH, maLop, hk, dDiem1T);
                        }
                        else if (txtDiem15p.Text != string.Empty && txtDiem1T.Text == string.Empty)
                        {
                            sqlInsert = "insert into Diem values('{0}','{1}',{2},{3},{4}, null)";
                            sqlInsert = string.Format(sqlInsert, maHS, maMH, maLop, hk, dDiem15p);
                        }
                        else
                        {
                            sqlInsert = "insert into Diem values('{0}','{1}',{2},{3},{4},{5})";
                            sqlInsert = string.Format(sqlInsert, maHS, maMH, maLop, hk, dDiem15p, dDiem1T);
                        }
                        dc.ExecNonQuery(sqlInsert);
                    }
                    loadDiem();
                }
                else
                {
                    MessageBox.Show("Bạn không dạy môn " + cbMon.SelectedItem.ToString());
                }
            }
        }

        

        

        

        
        

        
    }
}
